package db

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	_ "github.com/go-sql-driver/mysql"
	"grpc.getaway.adminspro.padmins.com/pkg/libs/utils"
	"reflect"
	"regexp"
	"sort"
	"strings"
	"time"
)

type Db struct {
	dbConnect        *DbConnect
	tx               *sql.Tx
	transactionTimes int
	result           sql.Result
	logger           DbLog
	ctx              context.Context
}

func New(dbConnect *DbConnect) *Db {
	return &Db{
		dbConnect:        dbConnect,
		tx:               nil,
		transactionTimes: 0,
	}
}

func (db *Db) Query(query string, args ...interface{}) ([]map[string]interface{}, *DbError) {
	defer db.slog(query, time.Now(), args...)
	var e error
	query, args, e = buildPrepareSqlBySlice(query, args)

	if e != nil {
		return nil, &DbError{Err: errors.New(" DB.buildPrepareSqlBySlice " + e.Error()), Sql: query}
	}

	var stmt *sql.Stmt
	if db.tx != nil {
		if db.ctx != nil {
			stmt, e = db.tx.PrepareContext(db.ctx, query)
		} else {
			stmt, e = db.tx.Prepare(query)
		}
	} else {
		if db.ctx != nil {
			stmt, e = db.dbConnect.connect.PrepareContext(db.ctx, query)
		} else {
			stmt, e = db.dbConnect.connect.Prepare(query)
		}
	}

	if e != nil {
		return nil, &DbError{Err: errors.New(" DB.Prepare " + e.Error()), Sql: GetRealSql(query, args...)}
	}

	defer stmt.Close()

	rows, e := stmt.Query(args...)

	if e != nil {
		return nil, &DbError{Err: errors.New(" stmt.Query " + e.Error()), Sql: GetRealSql(query, args...)}
	}
	defer rows.Close() // 关闭连接，不然会一直保持连接数据库

	columns, _ := rows.Columns()
	columnsLen := len(columns)
	list := make([]map[string]interface{}, 0)
	row := make([]interface{}, columnsLen)
	for key, _ := range row {
		var value interface{}
		row[key] = &value
	}

	for rows.Next() {
		_ = rows.Scan(row...)
		data := make(map[string]interface{})
		for key, value := range row {
			val := *value.(*interface{})
			switch val.(type) {
			case []byte:
				data[columns[key]] = string(val.([]byte))
				break
			default:
				data[columns[key]] = val
			}
		}
		list = append(list, data)
	}
	return list, nil
}

func (db *Db) Find(query string, args ...interface{}) (map[string]interface{}, *DbError) {
	query = fmt.Sprintf("%s limit 1", query)
	rows, e := db.Query(query, args...)
	if e != nil {
		return nil, e
	}

	if len(rows) > 0 {
		return rows[0], nil
	} else {
		return nil, nil
	}
}

func escapeIdentifier(identifier string) string {
	b := strings.Builder{}
	b.Grow(len(identifier) + 2)
	b.WriteString("`")
	b.WriteString(identifier)
	b.WriteString("`")
	return b.String()
}

func (db *Db) LastInsertId() (int64, *DbError) {
	lastInsertId, e := db.result.LastInsertId()
	if e != nil {
		return 0, &DbError{Err: errors.New(" LastInsertId " + e.Error())}
	}
	return lastInsertId, nil
}

func (db *Db) RowsAffected() (int64, *DbError) {
	rowsAffected, e := db.result.RowsAffected()
	if e != nil {
		return 0, &DbError{Err: errors.New(" RowsAffected " + e.Error())}
	}
	return rowsAffected, nil
}

func (db *Db) Insert(table string, data interface{}) (int64, *DbError) {

	sql, values := insert(table, data)
	result, dbErr := db.Execute(sql, values...)

	if dbErr != nil {
		return 0, dbErr
	}

	rowCount, e := result.RowsAffected()

	if e != nil {
		return 0, &DbError{Err: errors.New(" result.RowsAffected " + e.Error()), Sql: GetRealSql(sql, values...)}
	}

	return rowCount, nil
}

func insert(table string, data interface{}) (string, []interface{}) {

	var columns []string
	var sets []string
	var values []interface{}

	if reflect.TypeOf(data).Kind() == reflect.Map {
		l := len(data.(map[string]interface{}))
		columns = make([]string, l)
		set := make([]string, l)
		sets = make([]string, 1)
		values = make([]interface{}, l)
		i := 0
		for column, value := range data.(map[string]interface{}) {
			columns[i] = escapeIdentifier(column)
			set[i] = "?"
			values[i] = value
			i++
		}
		sets[0] = "(" + utils.Implode(",", set) + ")"
	} else {
		row := data.([]interface{})[0].(map[string]interface{})
		l := len(row)
		n := len(data.([]interface{}))
		i := 0
		columns = make([]string, l)
		set := make([]string, l)

		for column, _ := range row {
			columns[i] = escapeIdentifier(column)
			set[i] = "?"
			i++
		}

		setstring := utils.Implode(",", set)
		sets = make([]string, n)
		values = make([]interface{}, l*n)
		i = 0
		for key, record := range data.([]interface{}) {
			for _, column := range columns {
				value := record.(map[string]interface{})[strings.Trim(column, "`")]
				values[i] = value
				i++
			}
			sets[key] = "(" + setstring + ")"
		}
	}

	return fmt.Sprintf("INSERT INTO %s (%s) VALUES %s", escapeIdentifier(table), utils.Implode(",", columns), utils.Implode(",", sets)), values
}

func (db *Db) Update(table string, data map[string]interface{}, criteria map[string]interface{}) (int64, *DbError) {
	sql, values := update(table, data, criteria)
	result, dbErr := db.Execute(sql, values...)

	if dbErr != nil {
		return 0, dbErr
	}

	rowCount, e := result.RowsAffected()

	if e != nil {
		return 0, &DbError{Err: errors.New(" result.RowsAffected " + e.Error()), Sql: GetRealSql(sql, values...)}
	}

	return rowCount, nil
}

func update(table string, data map[string]interface{}, criteria map[string]interface{}) (string, []interface{}) {

	l := len(data)
	set := make([]string, l)
	values := make([]interface{}, l)
	i := 0

	for column, value := range data {
		column = escapeIdentifier(column)
		set[i] = column + "=?"
		values[i] = value
		i++
	}

	conditions, values := addCriteriaCondition(criteria, values)

	return fmt.Sprintf("UPDATE %s SET %s WHERE %s", escapeIdentifier(table), utils.Implode(",", set), utils.Implode(" AND ", conditions)), values
}

func (db *Db) Delete(table string, criteria map[string]interface{}) (int64, *DbError) {
	sql, values := delete(table, criteria)
	result, dbErr := db.Execute(sql, values...)

	if dbErr != nil {
		return 0, dbErr
	}

	rowCount, e := result.RowsAffected()

	if e != nil {
		return 0, &DbError{Err: errors.New(" result.RowsAffected " + e.Error()), Sql: GetRealSql(sql, values...)}
	}

	return rowCount, nil
}

func delete(table string, criteria map[string]interface{}) (string, []interface{}) {
	values := []interface{}{}
	conditions, values := addCriteriaCondition(criteria, values)
	return fmt.Sprintf("DELETE FROM %s WHERE %s", escapeIdentifier(table), utils.Implode(" AND ", conditions)), values
}

func addCriteriaCondition(criteria map[string]interface{}, values []interface{}) ([]string, []interface{}) {
	conditions := make([]string, len(criteria))
	i := 0
	for column, value := range criteria {
		if value == nil {
			conditions[i] = escapeIdentifier(column) + " IS NULL"
		} else if reflect.TypeOf(value).Kind() == reflect.Slice {
			conditions[i] = escapeIdentifier(column) + " IN (?)"
			values = append(values, value)
		} else {
			values = append(values, value)
			conditions[i] = escapeIdentifier(column) + "=?"
		}
		i++
	}

	return conditions, values
}

func (db *Db) Execute(query string, args ...interface{}) (sql.Result, *DbError) {
	defer db.slog(query, time.Now(), args...)

	var e error
	query, args, e = buildPrepareSqlBySlice(query, args)

	if e != nil {
		return nil, &DbError{Err: errors.New(" DB.buildPrepareSqlBySlice " + e.Error()), Sql: GetRealSql(query, args...)}
	}

	var stmt *sql.Stmt
	if db.tx != nil {
		stmt, e = db.tx.Prepare(query)
	} else {
		stmt, e = db.dbConnect.connect.Prepare(query)
	}

	if e != nil {
		return nil, &DbError{Err: e, Sql: GetRealSql(query, args...)}
	}

	defer stmt.Close()
	db.result, e = stmt.Exec(args...)

	if e != nil {
		return nil, &DbError{Err: e, Sql: GetRealSql(query, args...)}
	}
	return db.result, nil
}

// return businessError == nil commit, businessError != nil rollback
func (db *Db) TransactionFunc(fun func(*Db) (interface{}, error)) (interface{}, error) {
	e := db.beginTransaction()
	if e != nil {
		return nil, e
	}

	res, e := fun(db)

	if e != nil {
		if rollbackErr := db.rollback(); rollbackErr != nil {
			return nil, fmt.Errorf("db transaction failed: %v, unable to rollback: %v", e, rollbackErr)
		}
	} else {
		if commitErr := db.commit(); commitErr != nil {
			return nil, fmt.Errorf("db transaction failed: %v, unable to commit: %v", e, commitErr)
		}
	}

	return res, e
}

func (db *Db) beginTransaction() error {
	db.log("beginTransaction")
	if db.transactionTimes > 0 {
		db.transactionTimes++
		return nil
	}
	tx, e := db.dbConnect.connect.Begin()
	if e != nil {
		return e
	}
	db.tx = tx
	db.transactionTimes++
	return nil
}

func (db *Db) rollback() error {
	db.log("rollback")
	if db.transactionTimes == 0 {
		return errors.New("no begin transaction")
	}

	if db.transactionTimes == 1 {
		e := db.tx.Rollback()
		db.tx = nil
		return e
	}

	db.transactionTimes--
	return nil
}

func (db *Db) commit() error {
	db.log("commit")

	if db.transactionTimes == 0 {
		return errors.New("no begin transaction")
	}

	if db.transactionTimes == 1 {
		e := db.tx.Commit()
		db.tx = nil
		return e
	}

	db.transactionTimes--
	return nil
}

func (db *Db) SetLogger(logger DbLog) *Db {
	db.logger = logger
	return db
}

func (db *Db) log(a ...interface{}) {
	if db.logger != nil {
		db.logger.Print(append([]interface{}{"db"}, a...)...)
	}
}

func (db *Db) slog(sql string, t time.Time, vars ...interface{}) {
	if db.logger != nil {
		db.logger.Print("db sql", map[string]interface{}{
			"source":   sql,
			"vars":     vars,
			"duration": fmt.Sprintf("%.2fms", float64(time.Now().Sub(t).Nanoseconds())/1e6),
		})
	}
}

func (db *Db) SetContext(ctx context.Context) *Db {
	db.ctx = ctx
	return db
}

func GetSql(query string, args ...interface{}) (string, []interface{}, error) {
	reg, e := regexp.Compile(`\s{2,}`)
	if e != nil {
		return "", nil, e
	}
	query = reg.ReplaceAllString(query, " ")
	return buildPrepareSqlBySlice(query, args)
}

func GetRealSql(query string, args ...interface{}) string {
	reg, e := regexp.Compile(`\s{2,}`)
	if e != nil {
		return ""
	}
	query = reg.ReplaceAllString(query, " ")

	query, args, e = buildPrepareSqlBySlice(query, args)

	if e != nil {
		return ""
	}

	reg2, e := regexp.Compile(`\?`)

	if e != nil {
		return ""
	}

	i := 0
	return reg2.ReplaceAllStringFunc(query, func(s string) string {
		arg := args[i]
		if arg == nil {
			s = "NULL"
		} else if arg == true {
			s = "1"
		} else if arg == false {
			s = "0"
		} else {
			s = fmt.Sprintf("%v", arg)
			s = strings.ReplaceAll(strings.ReplaceAll(s, "'", "\\'"), "\"", "\\\"")
			s = "'" + s + "'"
		}
		i++
		return s
	})
}

func buildPrepareSqlBySlice(query string, args []interface{}) (string, []interface{}, error) {
	l := len(args)

	if l == 0 {
		return query, args, nil
	}

	var q strings.Builder
	var binds []interface{}
	c := 0

	for i, key := range query {
		if key == 63 {

			if l <= c {
				return query, args, errors.New("Too little parameters.")
			}

			// 如果绑定的参数类型是数组，自动生成数组对应的?号，一般用于条件为in的语句
			if reflect.TypeOf(args[c]).Kind() == reflect.Slice {
				str := ""
				for _, bind := range args[c].([]interface{}) {
					if str != "" {
						str += ","
					}
					str += "?"
					binds = append(binds, bind)
				}
				q.WriteString(str)
			} else {
				q.WriteString("?")
				binds = append(binds, args[c])
			}
			c++
		} else {
			q.WriteString(query[i : i+1])
		}
	}

	return q.String(), binds, nil
}

func buildPrepareSqlByMap(query string, args map[string]interface{}) (string, []interface{}, error) {
	var binds []interface{}
	l := len(args)
	keysIndex := make([]int, l)
	keys := make(map[int]string, l)
	i := 0

	for key, _ := range args {
		index := strings.Index(query, ":"+key)
		keysIndex[i] = index
		keys[index] = ":" + key
		i++
	}

	sort.Sort(sort.Reverse(sort.IntSlice(keysIndex)))

	for _, index := range keysIndex {
		key := keys[index]
		arg := args[key[1:]]

		if reflect.TypeOf(arg).Kind() == reflect.Slice {
			str := ""
			for _, bind := range arg.([]interface{}) {
				if str != "" {
					str += ","
				}
				str += "?"
				binds = append(binds, bind)
			}
			query = query[0:index] + str + query[index+len(key):]
		} else {
			binds = append(binds, arg)
			query = query[0:index] + "?" + query[index+len(key):]
		}
	}

	reverse := func(a []interface{}) []interface{} {
		for i := len(a)/2 - 1; i >= 0; i-- {
			opp := len(a) - 1 - i
			a[i], a[opp] = a[opp], a[i]
		}
		return a
	}

	return query, reverse(binds), nil
}
